# -*- coding: utf-8 -*-
"""
Excel导出器
"""
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
from typing import Dict
import os
from .base_exporter import BaseExporter

class ExcelExporter(BaseExporter):
    """Excel导出器"""
    
    def export(self, dict_data: Dict, file_path: str) -> bool:
        """
        导出为Excel格式
        
        Args:
            dict_data: 数据库字典数据
            file_path: 导出文件路径
            
        Returns:
            导出是否成功
        """
        try:
            if not self._validate_file_path(file_path):
                return False
            
            # 创建工作簿
            wb = Workbook()
            
            # 删除默认工作表
            wb.remove(wb.active)
            
            # 创建摘要工作表
            self._create_summary_sheet(wb, dict_data)
            
            if self.check_cancel():
                return False
            
            # 创建表结构工作表
            self._create_tables_sheet(wb, dict_data)
            
            if self.check_cancel():
                return False
            
            # 为每个表创建详细工作表
            tables = dict_data.get('tables', [])
            total_tables = len(tables)
            
            for i, table in enumerate(tables):
                if self.check_cancel():
                    return False
                
                self._create_table_detail_sheet(wb, table)
                
                # 更新进度
                progress = int((i + 1) / total_tables * 100)
                self.update_progress(progress, f"正在导出表: {table['name']}")
            
            # 保存文件
            wb.save(file_path)
            return True
            
        except Exception as e:
            print(f"Excel导出失败: {e}")
            return False
    
    def _create_summary_sheet(self, wb: Workbook, dict_data: Dict):
        """创建摘要工作表"""
        ws = wb.create_sheet("数据库摘要", 0)
        
        # 设置样式
        header_font = Font(bold=True, size=12)
        title_font = Font(bold=True, size=14)
        header_fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
        border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # 标题
        ws['A1'] = "MySQL数据库字典"
        ws['A1'].font = title_font
        ws.merge_cells('A1:D1')
        
        # 导出摘要
        summary = self._get_export_summary(dict_data)
        db_info = dict_data.get('database_info', {})
        
        row = 3
        summary_data = [
            ("导出时间", summary['export_time']),
            ("数据库名称", summary['database_name']),
            ("数据库版本", summary['database_version']),
            ("字符集", db_info.get('charset', '')),
            ("表总数", summary['total_tables']),
            ("字段总数", summary['total_columns']),
            ("索引总数", summary['total_indexes'])
        ]
        
        for label, value in summary_data:
            ws[f'A{row}'] = label
            ws[f'B{row}'] = value
            ws[f'A{row}'].font = header_font
            ws[f'A{row}'].fill = header_fill
            ws[f'A{row}'].border = border
            ws[f'B{row}'].border = border
            row += 1
        
        # 表列表
        row += 2
        ws[f'A{row}'] = "表列表"
        ws[f'A{row}'].font = title_font
        ws.merge_cells(f'A{row}:D{row}')
        
        row += 1
        headers = ["表名", "表注释", "字段数", "索引数"]
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=row, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = border
        
        # 表数据
        for table in dict_data.get('tables', []):
            row += 1
            ws[f'A{row}'] = table['name']
            ws[f'B{row}'] = table.get('comment', '')
            ws[f'C{row}'] = len(table.get('columns', []))
            ws[f'D{row}'] = len(table.get('indexes', []))
            
            for col in range(1, 5):
                ws.cell(row=row, column=col).border = border
        
        # 调整列宽
        ws.column_dimensions['A'].width = 20
        ws.column_dimensions['B'].width = 30
        ws.column_dimensions['C'].width = 15
        ws.column_dimensions['D'].width = 15
    
    def _create_tables_sheet(self, wb: Workbook, dict_data: Dict):
        """创建表结构总览工作表"""
        ws = wb.create_sheet("表结构总览")
        
        # 设置样式
        header_font = Font(bold=True, size=12)
        header_fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
        border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # 表头
        headers = ["表名", "字段名", "数据类型", "长度", "是否为空", "默认值", "主键", "字段注释"]
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = border
        
        # 数据
        row = 2
        for table in dict_data.get('tables', []):
            for column in table.get('columns', []):
                ws[f'A{row}'] = table['name']
                ws[f'B{row}'] = column['name']
                ws[f'C{row}'] = column['type_display']
                ws[f'D{row}'] = column.get('length', '')
                ws[f'E{row}'] = "是" if column['nullable'] else "否"
                ws[f'F{row}'] = column.get('default', '')
                ws[f'G{row}'] = "是" if column['key'] == 'PRI' else "否"
                ws[f'H{row}'] = column.get('comment', '')
                
                for col in range(1, 9):
                    ws.cell(row=row, column=col).border = border
                
                row += 1
        
        # 调整列宽
        column_widths = [20, 20, 15, 10, 10, 15, 10, 30]
        for i, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(i)].width = width
    
    def _create_table_detail_sheet(self, wb: Workbook, table: Dict):
        """创建表详细工作表"""
        # 工作表名称不能超过31个字符，且不能包含特殊字符
        sheet_name = table['name'][:31]
        sheet_name = ''.join(c for c in sheet_name if c.isalnum() or c in ('_', '-'))
        
        ws = wb.create_sheet(sheet_name)
        
        # 设置样式
        title_font = Font(bold=True, size=14)
        header_font = Font(bold=True, size=12)
        header_fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
        border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # 表标题
        ws['A1'] = f"表: {table['name']}"
        ws['A1'].font = title_font
        ws.merge_cells('A1:H1')
        
        if table.get('comment'):
            ws['A2'] = f"注释: {table['comment']}"
            ws.merge_cells('A2:H2')
        
        # 字段信息
        row = 4
        ws[f'A{row}'] = "字段信息"
        ws[f'A{row}'].font = title_font
        ws.merge_cells(f'A{row}:H{row}')
        
        row += 1
        headers = ["字段名", "数据类型", "长度", "精度", "小数位", "是否为空", "默认值", "字段注释"]
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=row, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = border
        
        # 字段数据
        for column in table.get('columns', []):
            row += 1
            ws[f'A{row}'] = column['name']
            ws[f'B{row}'] = column['type_display']
            ws[f'C{row}'] = column.get('length', '')
            ws[f'D{row}'] = column.get('precision', '')
            ws[f'E{row}'] = column.get('scale', '')
            ws[f'F{row}'] = "是" if column['nullable'] else "否"
            ws[f'G{row}'] = column.get('default', '')
            ws[f'H{row}'] = column.get('comment', '')
            
            for col in range(1, 9):
                ws.cell(row=row, column=col).border = border
        
        # 索引信息
        if table.get('indexes'):
            row += 2
            ws[f'A{row}'] = "索引信息"
            ws[f'A{row}'].font = title_font
            ws.merge_cells(f'A{row}:D{row}')
            
            row += 1
            index_headers = ["索引名", "索引类型", "是否唯一", "字段列表"]
            for col, header in enumerate(index_headers, 1):
                cell = ws.cell(row=row, column=col, value=header)
                cell.font = header_font
                cell.fill = header_fill
                cell.border = border
            
            # 索引数据
            for index in table['indexes']:
                row += 1
                ws[f'A{row}'] = index['name']
                ws[f'B{row}'] = index['type']
                ws[f'C{row}'] = "是" if index['unique'] else "否"
                ws[f'D{row}'] = ', '.join(index['columns'])
                
                for col in range(1, 5):
                    ws.cell(row=row, column=col).border = border
        
        # 调整列宽
        column_widths = [20, 15, 10, 10, 10, 10, 15, 30]
        for i, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(i)].width = width
